﻿--TBL_CUSTOMER_RELATION: check_code, chưa hoàn thành code chính xác để xác nhận mỗi quan hệ giữa cá nhân và doanh nghiệp.

exec('
DROP TABLE TBL_RELATION

SELECT  RECID AS CIF,REL_CODE,REL_CUSTOMER,CUS_REL_NAME,CUS_REL_LEGAL,CUS_REL_POS,REL_NAME into TBL_RELATION
FROM [SERVER16].[VPB_WHR2].DBO.VPB_CUSTOMER_RELATION 
WHERE cus_rel_pos is not null

select A.* INTO #A
from TBL_RELATION A 
join TBL_CUSTOMER B
ON A.CIF=B.CIF

ALTER TABLE TBL_RELATION
ADD BIR_OF_REL_CUST DATE, EMAIL_REL_CUS NVARCHAR(50), MOBILE_REL_CUS NVARCHAR (20)

ALTER TABLE TBL_RELATION
ADD ADDRESS_REL_CUS NVARCHAR(255), GENDER NVARCHAR(50)

UPDATE A
SET A.BIR_OF_REL_CUST=B.BIRTH_INCORP_DATE ,EMAIL_REL_CUS=b.EMAIL_ADDR,A.ADDRESS_REL_CUS=B.[ADDRESS],A.GENDER=B.VPB_GENDER
FROM TBL_RELATION A,SERVER74.BICDATA.DBO.CUSTOMER B
WHERE A.REL_CUSTOMER=B.RECID
AND REL_CUSTOMER IS NOT NULL')

-- update mobile cho người đại diệns
exec('
ALTER TABLE TBL_RELATION
ADD MOBILE_RELATION NVARCHAR(100)')
exec('
UPDATE A
SET MOBILE_RELATION=B.MOBILE
FROM TBL_RELATION A, TBL_CUSTOMER_CONTACT B
WHERE A.REL_CUSTOMER=B.CIF
AND REL_CUSTOMER IS NOT NULL
AND MOBILE IS NOT NULL')


--select * from [SERVER16].[VPB_WHR2].DBO.VPB_CUSTOMER_RELATION 